
import com.mongodb.MongoClient;
import com.mongodb.client.FindIterable;
import com.mongodb.client.MongoCollection;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.bson.Document;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;
import java.util.regex.Pattern;

public class ToExcel {
    public static void main(String[] args) throws IOException {

    }

    public static void writeToExcel() throws IOException {
        MongoClient mongoClient = new MongoClient("192.168.1.173",27017);
        MongoCollection<Document> collection = mongoClient.getDatabase("CCBD").getCollection("监管处罚_江苏监管局_TEST");

        Workbook workbook = new SXSSFWorkbook(10000);
        Sheet sheet = workbook.createSheet();

        List<String> keyIsNull = new ArrayList<>();
        List<String> valIsNull = new ArrayList<>();

        //excel行
        Row row = sheet.createRow(0);
//        int index = 0;
//        row.createCell(index++).setCellValue("_id");
//        row.createCell(index++).setCellValue("key");
//        row.createCell(index++).setCellValue("value");

        FindIterable<Document> documents = collection.find();


        Set<String> final_key_set = new HashSet<>();
        for (Document document: documents){
            Set<String> set = document.keySet();
            final_key_set.addAll(set);
        }

        int index = 0;
        Object[] objects = final_key_set.toArray();
        String[] strings = new String[objects.length];
        for (int i = 0; i < strings.length; i++) {
            strings[i] = (String) objects[i];
        }
        Arrays.sort(strings);
        for (String key : strings){
            row.createCell(index++).setCellValue(key);
        }

        int rownum = 1;
        int count = 0;
        for (Document document : documents) {
            int cellnum = 0;

            count++;
            Row row1 = sheet.createRow(rownum++);
            //row1.createCell(cellnum++).setCellValue(document.get("_id").toString());
            for (String key : strings){
                String val = null;

                try {
                    val = document.get(key).toString();
                }catch (Exception e){
                    val = "";
                }

                row1.createCell(cellnum++).setCellValue(val);
            }


            System.out.println("正在写入------------------------------");
        }

        workbook.write(new FileOutputStream("D:\\测试临时文件\\监管处罚_江苏监管局_TEST.xls"));
        System.out.println("写去excel完毕*--------------------");
        System.out.println("条目数量："+count);

        System.out.println("-----keyisnull---");
        for (String i : keyIsNull) {
            System.out.println(i);
        }

        System.out.println("\n\n--------valisnull------");
        for (String i : valIsNull){
            System.out.println(i);
        }
    }
}
